package com.beamfield.common;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @Description: excel处理工具
 * 1.读取现有excel;
 * 2.读取指定sheet；
 * 3.找到单元格，设置格式，填充数据
 * @author reece
 * @date 2017年12月5日 下午4:44:01
 */
public class ExcelUtil {

	private ExcelUtil() {
	}

	public static ExcelUtil getInstance() {
		return new ExcelUtil();
	}

	private WorkbookAgent wbAgent = new WorkbookAgent();

	private SheetAgent sheetAgent = new SheetAgent();

	private RowAgent rowAgent = new RowAgent();

	private CellAgent cellAgent = new CellAgent();

	public WorkbookAgent newWorkbook() {
		Workbook workbook = new XSSFWorkbook();
		wbAgent.setWorkbook(workbook);
		return wbAgent;
	}

	public WorkbookAgent load(InputStream in) {
		try {
			Workbook workbook = WorkbookFactory.create(in);
			wbAgent.setWorkbook(workbook);
		} catch (Exception e) {
			throw new RuntimeException("Excel读取失败！");
		}
		return wbAgent;
	}

	public void export(OutputStream out) {
		try {
			wbAgent.getWorkbook().write(out);
		} catch (Exception e) {
			throw new RuntimeException("Excel读取失败！");
		} finally {
			try {
				wbAgent.getWorkbook().close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 *
	 * @Description: workbook代理
	 * @author reece
	 * @date 2017年12月6日 上午10:04:52
	 */
	public class WorkbookAgent {

		private Workbook workbook;

		public SheetAgent sheet(int sheetnum) {
			Sheet sheet = null;
			try {
				sheet = workbook.getSheetAt(sheetnum);
			} catch (Exception e) {
				sheet = workbook.createSheet();
				workbook.setActiveSheet(sheetnum);
			}
			sheetAgent.setSheet(sheet);
			return sheetAgent;
		}

		public SheetAgent sheet(int sheetnum, String name) {
			Sheet sheet = null;
			try {
				sheet = workbook.getSheetAt(sheetnum);
			} catch (Exception e) {
				sheet = workbook.createSheet();
				workbook.setActiveSheet(sheetnum);
			}
			workbook.setSheetName(sheetnum, name);
			sheetAgent.setSheet(sheet);
			return sheetAgent;
		}

		public Workbook getWorkbook() {
			return workbook;
		}

		public void setWorkbook(Workbook workbook) {
			this.workbook = workbook;
		}

	}

	/**
	 *
	 * @Description: sheet代理
	 * @author reece
	 * @date 2017年12月6日 上午10:04:52
	 */
	public class SheetAgent {

		private Sheet sheet;

		public RowAgent row(int rownum) {
			Row row = sheet.getRow(rownum);
			if (row == null) {
				row = sheet.createRow(rownum);
			}
			rowAgent.setRow(row);
			return rowAgent;
		}

		public SheetAgent insertRow(int rownum, int rows) {
			sheet.shiftRows(rownum, sheet.getLastRowNum(), rows, true, false);
			return this;
		}

		public SheetAgent addMergeRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
			sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
			return this;
		}

		public WorkbookAgent and() {
			return wbAgent;
		}

		public Sheet getSheet() {
			return sheet;
		}

		public void setSheet(Sheet sheet) {
			this.sheet = sheet;
		}

	}

	/**
	 *
	 * @Description: row代理
	 * @author reece
	 * @date 2017年12月6日 上午10:04:52
	 */
	public class RowAgent {

		private Row row;

		public CellAgent cell(int cellnum) {
			Cell cell = row.getCell(cellnum);
			if (cell == null) {
				cell = row.createCell(cellnum);
			}
			cellAgent.setCell(cell);
			return cellAgent;
		}

		public RowAgent setDefaultStyle() {
			return setStyle("宋体", 12, false);
		}

		public RowAgent setStyle(String fontName, int fontSize, boolean bold) {
			return setStyle(fontName, fontSize, bold, IndexedColors.BLACK, HorizontalAlignment.CENTER,
					VerticalAlignment.CENTER);
		}

		public RowAgent setStyle(String fontName, int fontSize, boolean bold, IndexedColors color,
				HorizontalAlignment hAlign, VerticalAlignment vAlign) {
			return setStyle(fontName, fontSize, bold, color, hAlign, vAlign, BorderStyle.NONE);
		}

		public RowAgent setStyle(String fontName, int fontSize, boolean bold, IndexedColors color,
				HorizontalAlignment hAlign, VerticalAlignment vAlign, BorderStyle border) {
			CellStyle cellStyle = cellStyle(wbAgent.getWorkbook(), fontName, fontSize, bold, color, hAlign, vAlign, border);
			row.setRowStyle(cellStyle);
			return this;
		}

		public SheetAgent and() {
			return sheetAgent;
		}

		public Row getRow() {
			return row;
		}

		public void setRow(Row row) {
			this.row = row;
		}

	}

	/**
	 *
	 * @Description: cell代理
	 * @author reece
	 * @date 2017年12月6日 上午10:04:52
	 */
	public class CellAgent {

		private Cell cell;

		public CellAgent fillText(Object value) {
			if(value == null) {
				return this;
			}
			if (value instanceof String) {
				cell.setCellValue((String) value);
			}
			if (value instanceof Integer) {
				cell.setCellValue((Integer) value);
			}
			if (value instanceof Boolean) {
				cell.setCellValue((Boolean) value);
			}
			if (value instanceof Calendar) {
				cell.setCellValue((Calendar) value);
			}
			if (value instanceof Date) {
				cell.setCellValue((Date) value);
			}
			return this;
		}

		public CellAgent setDefaultStyle() {
			return setStyle("宋体", 12, false);
		}

		public CellAgent setStyle(String fontName, int fontSize, boolean bold) {
			return setStyle(fontName, fontSize, bold, IndexedColors.BLACK, HorizontalAlignment.CENTER,
					VerticalAlignment.CENTER);
		}

		public CellAgent setStyle(String fontName, int fontSize, boolean bold, IndexedColors color,
				HorizontalAlignment hAlign, VerticalAlignment vAlign) {
			return setStyle(fontName, fontSize, bold, color, hAlign, vAlign, BorderStyle.NONE);
		}

		public CellAgent setStyle(String fontName, int fontSize, boolean bold, IndexedColors color,
				HorizontalAlignment hAlign, VerticalAlignment vAlign, BorderStyle border) {
			CellStyle cellStyle = cellStyle(wbAgent.getWorkbook(), fontName, fontSize, bold, color, hAlign, vAlign,
					border);
			cell.setCellStyle(cellStyle);
			return this;
		}

		public RowAgent and() {
			return rowAgent;
		}

		public Cell getCell() {
			return cell;
		}

		public void setCell(Cell cell) {
			this.cell = cell;
		}

	}

	private CellStyle cellStyle(Workbook wb, String fontName, int fontSize, boolean bold, IndexedColors color,
			HorizontalAlignment hAlign, VerticalAlignment vAlign, BorderStyle border) {
		if (fontName == null) {
			fontName = "宋体";
		}
		if (fontSize == 0) {
			fontSize = 12;
		}
		if(color == null) {
			color = IndexedColors.BLACK;
		}
		if(hAlign == null) {
			hAlign = HorizontalAlignment.CENTER;
		}
		if(vAlign == null) {
			vAlign = VerticalAlignment.CENTER;
		}
		if(border == null) {
			border = BorderStyle.NONE;
		}
		CellStyle cellStyle = wb.createCellStyle(); // 单元格样式
		Font fontStyle = wb.createFont(); // 字体样式
		fontStyle.setBold(bold); // 加粗
		fontStyle.setColor(color.getIndex());
		fontStyle.setFontName(fontName); // 字体
		fontStyle.setFontHeightInPoints((short) fontSize); // 大小
		// 将字体样式添加到单元格样式中
		cellStyle.setFont(fontStyle);
		// 边框，居中
		cellStyle.setAlignment(hAlign);
		cellStyle.setVerticalAlignment(vAlign);
		cellStyle.setBorderBottom(border);
		cellStyle.setBorderLeft(border);
		cellStyle.setBorderRight(border);
		cellStyle.setBorderTop(border);
		return cellStyle;
	}

	public static void main(String[] args) throws FileNotFoundException {
		InputStream in = null;
		try {
			in = new BufferedInputStream(new FileInputStream(new File("/Users/reece/Downloads/test.xlsx")));
			File outfile = new File("/Users/reece/Downloads/out.xlsx");
			if (!outfile.exists()) {
				outfile.createNewFile();
			}
			OutputStream out = new BufferedOutputStream(new FileOutputStream(outfile));

			ExcelUtil excelUtil = ExcelUtil.getInstance();

			RowAgent rowAgent = excelUtil.newWorkbook().sheet(0, "测试").row(0);

			for(int i=0;i<10;i++) {
				rowAgent.cell(i).setDefaultStyle().fillText("标题"+i);
			}

			excelUtil.export(out);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		System.out.println("处理完毕！");
	}

	/**
	 * 得到cell的value
	 * @param cell
	 * @return
	 * @date 2018年6月8日
	 * @author mintonzhang@163.com
	 */
	public static String getCellValueAsString(Cell cell) {
		if (cell == null)
			return "";
		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue();
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
			return cell.getCellFormula();
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			short format = cell.getCellStyle().getDataFormat();
			System.out.println("format:"+format+";;;;;value:"+cell.getNumericCellValue());
			SimpleDateFormat sdf = null;
			if (format == 14 || format == 31 || format == 57 || format == 58
					|| (176<=format && format<=178) || (182<=format && format<=196)
					|| (210<=format && format<=213) || (208==format ) ) { // 日期
				sdf = new SimpleDateFormat("yyyy-MM-dd");
			} else if (format == 20 || format == 32 || format==183 || (200<=format && format<=209) ) { // 时间
				sdf = new SimpleDateFormat("HH:mm");
			} else { // 不是日期格式
				return String.valueOf(cell.getNumericCellValue());
			}
			double value = cell.getNumericCellValue();
			Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
			if(date==null || "".equals(date)){
				return "";
			}
			String result="";
			try {
				result = sdf.format(date);
			} catch (Exception e) {
				e.printStackTrace();
				return "";
			}
			return result;
		}
		return "";
	}

	/**
	 * 得到计算后的value
	 * @param cell
	 * @param formulaEvaluator
	 * @return
	 */
	public static String getCellValueFormula(Cell cell, FormulaEvaluator formulaEvaluator) {
		if (cell == null || formulaEvaluator == null) {
			return null;
		}

		if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
			return String.valueOf(formulaEvaluator.evaluate(cell).getNumberValue());
		}
		return getCellValueAsString(cell);
	}
	/**
     * 创建excel文档，
     * @param list 数据
     * @param keys list中map的key数组集合
     * @param columnNames excel的列名
     * */
    @SuppressWarnings("deprecation")
	public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) {
        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        String title = list.get(0).get("title").toString();
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for(int i=0;i<keys.length;i++){
            sheet.setColumnWidth((short) i, (short) (256*20+184));
        }
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,keys.length-1));

        CellStyle titleCS = wb.createCellStyle();
        titleCS.setBorderLeft(CellStyle.BORDER_THIN);
        titleCS.setBorderRight(CellStyle.BORDER_THIN);
        titleCS.setBorderTop(CellStyle.BORDER_THIN);
        titleCS.setBorderBottom(CellStyle.BORDER_THIN);
        titleCS.setAlignment(CellStyle.ALIGN_CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleCS.setFont(titleFont);
		Row titleRow = sheet.createRow(0);
        Cell titleRowCell = titleRow.createCell(0);
        titleRowCell.setCellValue(title == null?" ": title);
        titleRowCell.setCellStyle(titleCS);


        // 创建第一行
        Row row = sheet.createRow((short) 1);

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 14);
        f.setColor(IndexedColors.BLACK.getIndex());

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 12);
        f2.setColor(IndexedColors.BLACK.getIndex());

//        Font f3=wb.createFont();
//        f3.setFontHeightInPoints((short) 10);
//        f3.setColor(IndexedColors.RED.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        //设置列名
        for(int i=0;i<columnNames.length;i++){
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        //设置每行每列的值
        for (short i = 2; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow((short) i);
            // 在row行上创建一个方格
            for(short j=0;j<keys.length;j++){
                Cell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }


	/**
	 * 创建excel文档，人员导出
	 * @param list 数据
	 * @param keys list中map的key数组集合
	 * @param columnNames excel的列名
	 * */
	@SuppressWarnings("deprecation")
	public static Workbook createWorkBookPerson(List<Map<String, Object>> list,String []keys,String columnNames[]) {
		// 创建excel工作簿
		Workbook wb = new HSSFWorkbook();
		// 创建第一个sheet（页），并命名
		Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
		String title = list.get(0).get("title").toString();
		// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
		for(int i=0;i<keys.length;i++){
			sheet.setColumnWidth((short) i, (short) (256*20+184));
		}
		sheet.addMergedRegion(new CellRangeAddress(0,0,0,keys.length-1));

		CellStyle titleCS = wb.createCellStyle();
		titleCS.setBorderLeft(CellStyle.BORDER_THIN);
		titleCS.setBorderRight(CellStyle.BORDER_THIN);
		titleCS.setBorderTop(CellStyle.BORDER_THIN);
		titleCS.setBorderBottom(CellStyle.BORDER_THIN);
		titleCS.setAlignment(CellStyle.ALIGN_CENTER);
		Font titleFont = wb.createFont();
		titleFont.setFontHeightInPoints((short) 20);
		titleCS.setFont(titleFont);
		Row titleRow = sheet.createRow(0);
		Cell titleRowCell = titleRow.createCell(0);
		titleRowCell.setCellValue(title == null?" ": title);
		titleRowCell.setCellStyle(titleCS);


		// 创建第一行
		Row row = sheet.createRow((short) 1);

		// 创建两种单元格格式
		CellStyle cs = wb.createCellStyle();
		CellStyle cs2 = wb.createCellStyle();

		// 创建两种字体
		Font f = wb.createFont();
		Font f2 = wb.createFont();

		// 创建第一种字体样式（用于列名）
		f.setFontHeightInPoints((short) 14);
		f.setColor(IndexedColors.BLACK.getIndex());

		// 创建第二种字体样式（用于值）
		f2.setFontHeightInPoints((short) 12);
		f2.setColor(IndexedColors.BLACK.getIndex());

//        Font f3=wb.createFont();
//        f3.setFontHeightInPoints((short) 10);
//        f3.setColor(IndexedColors.RED.getIndex());

		// 设置第一种单元格的样式（用于列名）
		cs.setFont(f);
		cs.setBorderLeft(CellStyle.BORDER_THIN);
		cs.setBorderRight(CellStyle.BORDER_THIN);
		cs.setBorderTop(CellStyle.BORDER_THIN);
		cs.setBorderBottom(CellStyle.BORDER_THIN);
		cs.setAlignment(CellStyle.ALIGN_CENTER);

		// 设置第二种单元格的样式（用于值）
		cs2.setFont(f2);
		cs2.setBorderLeft(CellStyle.BORDER_THIN);
		cs2.setBorderRight(CellStyle.BORDER_THIN);
		cs2.setBorderTop(CellStyle.BORDER_THIN);
		cs2.setBorderBottom(CellStyle.BORDER_THIN);
		cs2.setAlignment(CellStyle.ALIGN_CENTER);
		//设置列名
		for(int i=0;i<columnNames.length;i++){
			Cell cell = row.createCell(i);
			cell.setCellValue(columnNames[i]);
			cell.setCellStyle(cs);
		}
		//设置每行每列的值
		for (short i = 1; i < list.size(); i++) {
			// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
			// 创建一行，在页sheet上
			Row row1 = sheet.createRow((short) i+1);
			// 在row行上创建一个方格
			for(short j=0;j<keys.length;j++){
				Cell cell = row1.createCell(j);
				cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
				cell.setCellStyle(cs2);
			}
		}
		return wb;
	}

	public static void drawPictureInfoExcel(HSSFWorkbook wb, HSSFPatriarch patriarch, short colIndex, int rowIndex, InputStream inStream){
		//rowIndex代表当前行
		try {
			//得到图片的二进制数据，以二进制封装得到数据，具有通用性
			byte[] data = readInputStream(inStream);
			//anchor主要用于设置图片的属性
			HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 250, colIndex, rowIndex, colIndex, rowIndex);
			//Sets the anchor type （图片在单元格的位置）
			//0 = Move and size with Cells, 2 = Move but don't size with cells, 3 = Don't move or size with cells.
			anchor.setAnchorType(3);
			patriarch.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static byte[] readInputStream(InputStream inStream) throws Exception{
		ByteArrayOutputStream outStream = new ByteArrayOutputStream();
		//创建一个Buffer字符串
		byte[] buffer = new byte[1024];
		//每次读取的字符串长度，如果为-1，代表全部读取完毕
		int len = 0;
		//使用一个输入流从buffer里把数据读取出来
		while( (len=inStream.read(buffer)) != -1 ){
			//用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
			outStream.write(buffer, 0, len);
		}
		//关闭输入流
		inStream.close();
		//把outStream里的数据写入内存
		return outStream.toByteArray();
	}

}
